package com.fs.ibox_h.dao.impl;

import com.fs.ibox_h.dao.CollectionDao;
import com.fs.ibox_h.pojo.Collection;
import com.fs.ibox_h.util.DateUtil;
import com.fs.ibox_h.util.JDBCUtil;
import com.fs.ibox_h.vo.CollectionCondition;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Author:HOU
 * date:2022/11/15  15:31
 * 案例:
 **/
public class CollectionDaoimpl implements CollectionDao {
    @Override
    public List<Collection> selectAll() {
        String sql="SELECT * FROM tb_collection ORDER BY startdate desc";
        List<Collection> tbCollections = JDBCUtil.executeQuery(sql, Collection.class);
        return tbCollections;
    }

    @Override
    public void addCollection(Collection collection) {
        String sql="INSERT INTO tb_collection (cname,type,releasenum,price,content,startdate,enddate,cimg,residual) VALUES (?,?,?,?,?,?,?,?,?)";
        JDBCUtil.executeUpdate(sql,collection.getCname(),collection.getType(),collection.getReleasenum(),collection.getPrice(),collection.getContent(), DateUtil.toString(collection.getStartdate(),"yyyy-MM-dd HH:mm:ss"),DateUtil.toString(collection.getEnddate(),"yyyy-MM-dd HH:mm:ss"),collection.getCimg(),collection.getReleasenum());
    }

    @Override
    public List<Collection> selectByCnameState(int currentPageInt, int pageSizeInt,CollectionCondition collectionCondition) {
        StringBuffer  sql =new StringBuffer("SELECT * FROM tb_collection where 1=1 ");
        List<Object> paramList = new ArrayList<>();
        appedSql(sql,paramList,collectionCondition);
        sql.append(" limit ? , ?");
        paramList.add((currentPageInt-1)*pageSizeInt);
        paramList.add(pageSizeInt);
        List<Collection> collections = JDBCUtil.executeQuery(sql.toString(), Collection.class, paramList.toArray());
        return collections;
    }

    @Override
    public  int count(CollectionCondition collectionCondition) {
        //String 不可变字符串
        //StringBuffer(线程安全) StringBuilder(线程不安全)
        StringBuffer sql = new StringBuffer(" SELECT COUNT(1) FROM tb_collection WHERE 1=1 ");
        //给?赋的值  值的顺序与?顺序一致
        List<Object> paramList = new ArrayList<>();
        //拼接sql
        appedSql(sql,paramList,collectionCondition);

        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            psmt = conn.prepareStatement(sql.toString());
            //给?赋值
            for (int i = 0; i < paramList.size(); i++) {
                psmt.setObject(i+1,paramList.get(i));
            }
            rs = psmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(rs,psmt,conn);
        }
        return 0;
    }

    private void appedSql(StringBuffer sql, List<Object> paramList, CollectionCondition collectionCondition){
        if (collectionCondition!=null){
            if(collectionCondition.getCname()!=null && !collectionCondition.getCname().isEmpty()){
                sql.append("AND cname like ? ");
                paramList.add("%"+collectionCondition.getCname()+"%");
            }
            if (collectionCondition.getCstate()!=null ){
                if (collectionCondition.getCstate()==0){
                    System.out.println(collectionCondition.getCstate());
                    sql.append("AND startdate >?");
                    paramList.add(DateUtil.toString(new Date(),"yyyy-MM-dd HH:mm:ss"));
                }
                else if(collectionCondition.getCstate()==1){
                    System.out.println(collectionCondition.getCstate());
                    sql.append("AND residual =0");
                }
                else if(collectionCondition.getCstate()==2){
                    System.out.println(collectionCondition.getCstate());
                    sql.append("AND startdate<? AND residual>0 and enddate>?");
                    paramList.add(DateUtil.toString(new Date(),"yyyy-MM-dd HH:mm:ss"));
                    paramList.add(DateUtil.toString(new Date(),"yyyy-MM-dd HH:mm:ss"));
                }
                else if(collectionCondition.getCstate()==3){
                    System.out.println(collectionCondition.getCstate());
                    sql.append("and enddate<?");
                    paramList.add(DateUtil.toString(new Date(),"yyyy-MM-dd HH:mm:ss"));
                }

            }
        }
    }

//    private  void  appedSql(StringBuffer sql, UserCondition userCondition,List<Object> paramList){
//        if(userCondition != null){
//            if(userCondition.getName() != null && !userCondition.getName().isEmpty() ){
//                sql.append("  AND name like ? ");
//                paramList.add("%"+userCondition.getName()+"%");
//            }
//            if(userCondition.getAddress() != null && !userCondition.getAddress().isEmpty() ){
//                sql.append(" AND  address = ? ");
//                paramList.add(userCondition.getAddress());
//            }
//            if(userCondition.getEmail() != null && !userCondition.getEmail().isEmpty() ){
//                sql.append(" AND  email like ? ");
//                paramList.add("%"+userCondition.getEmail()+"%");
//            }
//            if(userCondition.getStartAge() != null  ){
//                sql.append(" AND  age >= ? ");
//                paramList.add(userCondition.getStartAge());
//            }
//            if(userCondition.getEndAge() != null  ){
//                sql.append(" AND  age <= ? ");
//                paramList.add(userCondition.getEndAge());
//            }
//        }
}
